﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using CoreEx.Database.Query.Expressions;
using CoreEx.Common.Extensions;
using LinFu.IoC.Configuration;

namespace CoreEx.Database.Query.Implementation
{
    /// <summary>
    /// Translates a <see cref="DbExpression"/> into a SQL Server specific string representation.
    /// </summary>
    [Implements(typeof(DbQueryTranslator), ServiceName = "System.Data.SqlClient")]
    public class SqlQueryTranslator : DbQueryTranslator
    {
                
        /// <summary>
        /// Translates the <paramref name="binaryExpression"/> into a string representation.
        /// </summary>
        /// <param name="binaryExpression">The <see cref="DbBinaryExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitBinaryExpression(DbBinaryExpression binaryExpression)
        {
            var body = "({0}{1}{2})";
            if (binaryExpression.BinaryExpressionType == DbBinaryExpressionType.Assignment)            
                body = "SET {0} = {2}";
            
            
            
            var sqlFragment = string.Format(body, Visit(binaryExpression.LeftExpression), 
                GetBinaryOperator(binaryExpression.BinaryExpressionType),
                                 Visit(binaryExpression.RightExpression));
            return sqlFragment;
        }

        /// <summary>
        /// Translates the <paramref name="stringFunctionExpression"/> into a string representation.
        /// </summary>
        /// <param name="stringFunctionExpression">The <see cref="DbStringFunctionExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitStringFunctionExpression(DbStringFunctionExpression stringFunctionExpression)
        {
            var functionName = GetStringFunctionBody(stringFunctionExpression.StringFunctionExpressionType);
            var functionSyntax = CreateDefaultFunctionSyntax(functionName, stringFunctionExpression.Arguments);
            return functionSyntax;
        }

        /// <summary>
        /// Translates the <paramref name="aggregateFunctionExpression"/> into a string representation.
        /// </summary>
        /// <param name="aggregateFunctionExpression">The <see cref="DbAggregateFunctionExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitAggregateFunctionExpression(DbAggregateFunctionExpression aggregateFunctionExpression)
        {
            var functionName = GetAggregateFunctionBody(aggregateFunctionExpression.AggregateFunctionExpressionType);
            var functionSyntax = CreateDefaultFunctionSyntax(functionName, aggregateFunctionExpression.Arguments);
            return functionSyntax;
        }

        /// <summary>
        /// Translates the <paramref name="dateTimeFunctionExpression"/> into a string representation.
        /// </summary>
        /// <param name="dateTimeFunctionExpression">The <see cref="DbDateTimeFunctionExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitDateTimeFunctionExpression(DbDateTimeFunctionExpression dateTimeFunctionExpression)
        {
            string body = GetDateTimeFunctionBody(dateTimeFunctionExpression.DateTimeFunctionExpressionType);            
            switch (dateTimeFunctionExpression.DateTimeFunctionExpressionType)
            {
                case DbDateTimeFunctionExpressionType.AddYears:                    
                case DbDateTimeFunctionExpressionType.AddMonths:                    
                case DbDateTimeFunctionExpressionType.AddDays:                    
                case DbDateTimeFunctionExpressionType.AddHours:                    
                case DbDateTimeFunctionExpressionType.AddMinutes:                    
                case DbDateTimeFunctionExpressionType.AddSeconds:                    
                case DbDateTimeFunctionExpressionType.AddMilliseconds:
                    return CreateDefaultFunctionSyntax(body, dateTimeFunctionExpression.Arguments.Reverse());                    
                default:
                    return CreateDefaultFunctionSyntax(body, dateTimeFunctionExpression.Arguments);                    
            }
        }




        /// <summary>
        /// Translates the <paramref name="selectExpression"/> into a string representation.
        /// </summary>
        /// <param name="selectExpression">The <see cref="DbSelectExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitSelectExpression(DbSelectExpression selectExpression)
        {
            var sb = new StringBuilder();
            if (!selectExpression.ProjectionExpression.IsNull())
            {
                sb.Append("SELECT ");
                if (!selectExpression.TakeExpression.IsNull())                
                    sb.AppendFormat("TOP({0}) ", Visit(selectExpression.TakeExpression));
                sb.AppendLine();
                sb.AppendFormat(1, "{0} ", Visit(selectExpression.ProjectionExpression));
            }

            if (!selectExpression.FromExpression.IsNull())
            {
                sb.AppendLine();
                sb.AppendLine("FROM ");
                sb.AppendFormat(1, "{0} ", Visit(selectExpression.FromExpression));
            }

            if (!selectExpression.WhereExpression.IsNull())
            {
                sb.AppendLine();
                sb.AppendLine("WHERE ");
                sb.AppendFormat(1, "{0} ", Visit(selectExpression.WhereExpression));
            }

            if (!selectExpression.OrderByExpression.IsNull())
            {
                sb.AppendLine();
                sb.AppendLine("ORDER BY ");
                sb.AppendFormat(1, "{0} ", Visit(selectExpression.OrderByExpression));
            }

            if (selectExpression.IsSubQuery)
                return string.Format("({0})", sb.ToString().Trim());

            return sb.ToString().Trim();
        }

        /// <summary>
        /// Translates the <paramref name="columnExpression"/> into a string representation.
        /// </summary>
        /// <param name="columnExpression">The <see cref="DbColumnExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitColumnExpression(DbColumnExpression columnExpression)
        {
            return QuoteIdentifier(columnExpression.ColumnName);
        }

        /// <summary>
        /// Translates the <paramref name="tableExpression"/> into a string representation.
        /// </summary>
        /// <param name="tableExpression">The <see cref="DbTableExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitTableExpression(DbTableExpression tableExpression)
        {
            return QuoteIdentifier(tableExpression.TableName);
        }

        /// <summary>
        /// Translates the <paramref name="constantExpression"/> into a string representation.
        /// </summary>
        /// <param name="constantExpression">The <see cref="DbConstantExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitConstantExpression(DbConstantExpression constantExpression)
        {
            var parameterName = string.Format("@p{0}", Parameters.Count());
            CreateParameter(parameterName,constantExpression.Value);
            return parameterName;
        }

        /// <summary>
        /// Translates the <paramref name="aliasExpression"/> into a string representation.
        /// </summary>
        /// <param name="aliasExpression">The <see cref="DbAliasExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitAliasExpression(DbAliasExpression aliasExpression)
        {
            string syntax = string.Format("{0} AS {1}", Visit(aliasExpression.Target), aliasExpression.Alias);
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="concatExpression"/> into a string representation.
        /// </summary>
        /// <param name="concatExpression">The <see cref="DbConcatExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitConcatExpression(DbConcatExpression concatExpression)
        {
            string syntax = string.Format("{0} {1}", Visit(concatExpression.LeftExpression),
                                          Visit(concatExpression.RightExpression));
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="conditionalExpression"/> into a string representation.
        /// </summary>
        /// <param name="conditionalExpression">The <see cref="DbConditionalExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitConditionalExpression(DbConditionalExpression conditionalExpression)
        {
            string syntax = string.Format("CASE WHEN {0} THEN {1} ELSE {2} END", Visit(conditionalExpression.Condition),
                                          Visit(conditionalExpression.IfTrue), Visit(conditionalExpression.IfFalse));
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="existsExpression"/> into a string representation.
        /// </summary>
        /// <param name="existsExpression">The <see cref="DbExistsExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitExistsExpression(DbExistsExpression existsExpression)
        {
            var syntax = string.Format("EXISTS{0}", Visit(existsExpression.SubSelectExpression));
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="listExpression"/> into a string representation.
        /// </summary>
        /// <param name="listExpression">The <see cref="DbListExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitListExpression(DbListExpression listExpression)
        {
            if (listExpression.Count() == 0)
                return string.Empty;
            var syntax =  listExpression.Select(e => Visit(e)).Aggregate((current, next) => current + "," + next);
            return syntax;
        }


        /// <summary>
        /// Translates the <paramref name="inExpression"/> into a string representation.
        /// </summary>
        /// <param name="inExpression">The <see cref="DbInExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitInExpression(DbInExpression inExpression)
        {
            string body = "{0} IN({1})";
            if (inExpression.Expression.ExpressionType == DbExpressionType.Query)
                body = "{0} IN{1}";                       
            string syntax = string.Format(body,Visit(inExpression.Target),Visit(inExpression.Expression));
            return syntax;            
        }

        /// <summary>
        /// Translates the <paramref name="joinExpression"/> into a string representation.
        /// </summary>
        /// <param name="joinExpression">The <see cref="DbJoinExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitJoinExpression(DbJoinExpression joinExpression)
        {
            string body;
            switch (joinExpression.JoinExpressionType)
            {
                case DbJoinExpressionType.InnerJoin:
                    body = "INNER JOIN {0} ON {1}";
                    break;
                case DbJoinExpressionType.LeftOuterJoin:
                    body = "LEFT OUTER JOIN {0} ON {1}";
                    break;
                case DbJoinExpressionType.RightOuterJoin:
                    body = "RIGHT OUTER JOIN {0} ON {1}";
                    break;
                default:
                    throw new ArgumentOutOfRangeException("joinExpression",
                                                          string.Format(
                                                              "The JoinExpressionType '{0}' is not supported",
                                                              joinExpression.JoinExpressionType));
            }

            string syntax = string.Format(body, Visit(joinExpression.Target), Visit(joinExpression.Condition));
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="unaryExpression"/> into a string representation.
        /// </summary>
        /// <param name="unaryExpression">The <see cref="DbUnaryExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitUnaryExpression(DbUnaryExpression unaryExpression)
        {
            string body;

            switch (unaryExpression.UnaryExpressionType)
            {
                case DbUnaryExpressionType.Not:
                    body = "NOT {0}";
                    break;
                default:
                    throw new ArgumentOutOfRangeException("unaryExpression",
                                                          string.Format(
                                                              "The UnaryExpressionType '{0}' is not supported",
                                                              unaryExpression.UnaryExpressionType));
            }
            string syntax = string.Format(body, Visit(unaryExpression.Operand));
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="orderByExpression"/> into a string representation.
        /// </summary>
        /// <param name="orderByExpression">The <see cref="DbOrderByExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitOrderByExpression(DbOrderByExpression orderByExpression)
        {
            string body = orderByExpression.OrderByDirection == DbOrderByExpressionType.Ascending
                              ? "ORDER BY {0} ASC"
                              : "ORDER BY {0} DESC";
            string syntax = string.Format(body, Visit(orderByExpression.Expression));
            return syntax;
        }

        /// <summary>
        /// Translates the <paramref name="prefixExpression"/> into a string representation.
        /// </summary>
        /// <param name="prefixExpression">The <see cref="prefixExpression"/> to translate.</param>
        /// <returns><see cref="string"/></returns>
        protected override string VisitPrefixExpression(DbPrefixExpression prefixExpression)
        {
            string syntax = string.Format("[{0}].{1}", prefixExpression.Prefix, Visit(prefixExpression.Target));
            return syntax;
        }


        /// <summary>
        /// Gets the textual representation of the <paramref name="binaryExpressionType"/>
        /// </summary>
        /// <param name="binaryExpressionType">The <see cref="DbBinaryExpressionType"/> for which to return the textual representation.</param>
        /// <returns><see cref="string"/></returns>
        protected virtual string GetBinaryOperator(DbBinaryExpressionType binaryExpressionType)
        {
            switch (binaryExpressionType)
            {
                case DbBinaryExpressionType.And:
                    return " AND ";
                case DbBinaryExpressionType.Or:
                    return " OR ";                
                case DbBinaryExpressionType.Equal:
                case DbBinaryExpressionType.Assignment:
                    return " = ";
                case DbBinaryExpressionType.NotEqual:
                    return " <> ";
                case DbBinaryExpressionType.GreaterThanOrEqual:
                    return " >= ";
                case DbBinaryExpressionType.GreaterThan:
                    return " > ";
                case DbBinaryExpressionType.LessThan:
                    return " < ";
                case DbBinaryExpressionType.LessThanOrEqual:
                    return " <= ";
                case DbBinaryExpressionType.Add:
                    return " + ";
                case DbBinaryExpressionType.Subtract:
                    return " - ";
                case DbBinaryExpressionType.Multiply:
                    return " * ";
                case DbBinaryExpressionType.Divide:
                    return " / ";                
                default:
                    throw new ArgumentOutOfRangeException("binaryExpressionType", binaryExpressionType,"is not supported");
            }
        }

        /// <summary>
        /// Gets the textual representation of the <paramref name="aggregateFunctionExpressionType"/>
        /// </summary>
        /// <param name="aggregateFunctionExpressionType">The <see cref="DbAggregateFunctionExpressionType"/> for which to return the textual representation.</param>
        /// <returns><see cref="string"/></returns>
        protected virtual string GetAggregateFunctionBody(DbAggregateFunctionExpressionType aggregateFunctionExpressionType)
        {
            switch (aggregateFunctionExpressionType)
            {
                case DbAggregateFunctionExpressionType.Avg:
                    return "AVG({0})";
                case DbAggregateFunctionExpressionType.Count:
                    return "COUNT({0})";
                case DbAggregateFunctionExpressionType.Max:
                    return "MAX({0})";
                case DbAggregateFunctionExpressionType.Min:
                    return "MIN({0})";
                case DbAggregateFunctionExpressionType.Sum:
                    return "SUM({0})";
                default:
                    throw new ArgumentOutOfRangeException("aggregateFunctionExpressionType", aggregateFunctionExpressionType, " is not supported");
            }
        }

        /// <summary>
        /// Creates the default syntax for calling the function identified by <paramref name="functionBody"/>
        /// </summary>
        /// <param name="functionBody">The function body without the actual arguments</param>
        /// <param name="arguments">A list of <see cref="DbExpression"/> instances representing the function arguments.</param>
        /// <returns><see cref="string"/></returns>
        protected virtual string CreateDefaultFunctionSyntax(string functionBody, IEnumerable<DbExpression> arguments)
        {
            if (arguments.Count() == 0)
                return functionBody;
            var argumentString = arguments.Select(a => Visit(a)).Aggregate((current,next) => current + "," + next);
            var functionCall = string.Format(functionBody, argumentString);
            return functionCall;
        }

        /// <summary>
        /// Returns the name of the function as it is defined in the target DBMS.
        /// </summary>
        /// <param name="stringFunctionExpressionType">The <see cref="DbStringFunctionExpressionType"/> that specifies the function name to return.</param>
        /// <returns><see cref="string"/></returns>
        protected virtual string GetStringFunctionBody(DbStringFunctionExpressionType stringFunctionExpressionType)
        {
            switch (stringFunctionExpressionType)
            {
                case DbStringFunctionExpressionType.Length:
                    return "LEN({0})";
                case DbStringFunctionExpressionType.Replace:
                    return "REPLACE({0})";
                case DbStringFunctionExpressionType.Reverse:
                    return "REVERSE({0})";
                case DbStringFunctionExpressionType.ToLower:
                    return "LOWER({0})";
                case DbStringFunctionExpressionType.ToUpper:
                    return "UPPER({0})";
                case DbStringFunctionExpressionType.Trim:
                    return "LTRIM(RTRIM({0}))";
                case DbStringFunctionExpressionType.TrimStart:
                    return "LTRIM({0})";
                case DbStringFunctionExpressionType.TrimEnd:
                    return "RTRIM({0})";
                case DbStringFunctionExpressionType.SoundEx:
                    return "SOUNDEX({0})";
                case DbStringFunctionExpressionType.SubString:
                    return "SUBSTR({0})";
                default:
                    throw new ArgumentOutOfRangeException("stringFunctionExpressionType",stringFunctionExpressionType," is not supported");
            }

        }

        protected virtual string GetDateTimeFunctionBody(DbDateTimeFunctionExpressionType dateTimeFunctionExpressionType)
        {
            switch(dateTimeFunctionExpressionType)
            {
                case DbDateTimeFunctionExpressionType.AddYears:
                    return "DATEADD(year,{0})";
                case DbDateTimeFunctionExpressionType.AddMonths:
                    return "DATEADD(month,{0})";
                case DbDateTimeFunctionExpressionType.AddDays:
                    return "DATEADD(day,{0})";
                case DbDateTimeFunctionExpressionType.AddHours:
                    return "DATEADD(hour,{0})";
                case DbDateTimeFunctionExpressionType.AddMinutes:
                    return "DATEADD(minute,{0})";
                case DbDateTimeFunctionExpressionType.AddSeconds:
                    return "DATEADD(second,{0})";
                case DbDateTimeFunctionExpressionType.AddMilliseconds:
                    return "DATEADD(millisecond,{0})";
                case DbDateTimeFunctionExpressionType.Date:                
                    return "CONVERT(DATETIME,CONVERT(VARCHAR,{0},102))";
                case DbDateTimeFunctionExpressionType.DayOfMonth:
                    return "DATEPART(day,{0})";
                case DbDateTimeFunctionExpressionType.DayOfWeek:
                    return "DATEPART(weekday,{0})";
                case DbDateTimeFunctionExpressionType.DayOfYear:
                    return "DATEPART(dayofyear,{0})";
                case DbDateTimeFunctionExpressionType.Year:
                    return "DATEPART(year,{0})";
                case DbDateTimeFunctionExpressionType.Month:
                    return "DATEPART(month,{0})";
                case DbDateTimeFunctionExpressionType.Hour:
                    return "DATEPART(hour,{0})";
                case DbDateTimeFunctionExpressionType.Minute:
                    return "DATEPART(minute,{0})";
                case DbDateTimeFunctionExpressionType.Second:
                    return "DATEPART(second,{0})";
                case DbDateTimeFunctionExpressionType.MilliSecond:
                    return "DATEPART(millisecond,{0})";
                case DbDateTimeFunctionExpressionType.Now:
                    return "GETDATE()";
                
                default:
                    throw new ArgumentOutOfRangeException("dateTimeFunctionExpressionType",
                                                          string.Format(
                                                              "The DateTimeFunctionExpressionType '{0}' is not supported",
                                                              dateTimeFunctionExpressionType));

            }
        }

    }
}
